package com.ef.dao.impl;

import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

import org.hibernate.Query;
import org.hibernate.SQLQuery;

import com.ef.dao.ContractDao;
import com.ef.domain.Contract;
import com.ef.web.domain.CCPerformance;
import com.ef.web.domain.CRPerformance;
import com.ef.web.domain.Financial;
import com.ef.web.domain.RevenueCount;
import com.ef.web.domain.TMPerformance;

public class ContractDaoImpl extends DaoImpl<Contract> implements ContractDao {

	/**
	 * Revenue By CC
	 */
	@SuppressWarnings("unchecked")
	public List<RevenueCount> sumByCC(Date start ,Date end, Long schoolId, String ccList) {
		SimpleDateFormat formater = new SimpleDateFormat("yyyyMMdd");
		SimpleDateFormat formater2 = new SimpleDateFormat("yyyy-MM-dd");
		String _start = formater.format(start);
		int _start_year = Integer.valueOf(_start.substring(0, 4));
		String _end = formater.format(end);
		int _end_year = Integer.valueOf(_end.substring(0, 4));
		Date lastStart = null;
		Date lastEnd = null;
		try {
		  lastStart =	formater.parse((_start_year-1)+_start.substring(4));
		  lastEnd = formater.parse((_end_year-1)+_end.substring(4));
		} catch (ParseException e) {
			 
			e.printStackTrace();
		}
		String strStart = formater2.format(start);
		String strEnd = formater2.format(end);
		String strLastStart = formater2.format(lastStart);
		String strLastEnd = formater2.format(lastEnd);
		List<RevenueCount> result = new ArrayList<RevenueCount>();
		StringBuffer sql = new StringBuffer("select name, a.pay sum1, b.pay sum2  from (select * from (select a.id, a.name from ef_user a, ef_role b where");
		if(ccList!=null) {
			sql.append(" a.id in (");
			sql.append(ccList);
			sql.append(") and ");
		}
		sql.append(" a.school_id=");
		sql.append(schoolId.toString());
		sql.append(" and a.role_id=b.id and a.status='VALID' and b.name='CC') c left join (select sum(case when paytype='Pay' then pay else -pay end) as pay, b.createuser_id from ef_payhistory a, ef_contract b where a.contract_id=b.id and paytime<=to_date('");
		sql.append(strEnd);
		sql.append(" 23:59:59','yyyy-MM-dd hh24:mi:ss') and paytime>=to_date('");
		sql.append(strStart);
		sql.append("','yyyy-MM-dd') group by createuser_id) d  on c.id=d.createuser_id) a left join (select sum(case when paytype='Pay' then pay else -pay end) as pay, b.createuser_id from ef_payhistory a, ef_contract b where a.contract_id=b.id and paytime<=to_date('");
		sql.append(strLastEnd);
		sql.append(" 23:59:59','yyyy-MM-dd hh24:mi:ss') and paytime>=to_date('");
		sql.append(strLastStart);
		sql.append("','yyyy-MM-dd') group by createuser_id ) b on a.id=b.createuser_id");
//		sql.append(" SELECT A.name ,A.r1, B.r2 FROM ");
//		sql.append(" (SELECT ").append(" u.name, SUM(paysum-refundsum) AS r1 ");
//		sql.append(" FROM ( SELECT contract_id  AS contractid , SUM(DECODE(h.paytype,'Pay',h.pay,0)) AS paysum ,");
//		sql.append(" SUM(DECODE(h.paytype,'Refund',h.pay,0)) AS refundsum  FROM ef_payhistory    h ");
//		sql.append(" WHERE h.paytime BETWEEN TO_DATE('").append(strStart).append("','yyyy-MM-dd')");
//		sql.append(" AND TO_DATE('").append(strEnd).append("','yyyy-MM-dd') ");
//		sql.append(" GROUP BY h.contract_id )    p ,");
//		sql.append(" ef_contract c ,ef_leads l, ef_user u WHERE ");
//		sql.append(" p.contractid = c.id ");
//		sql.append(" AND c.leads_id = l.id ");
//		sql.append(" AND l.cc_id = u.id ");
//		sql.append(" GROUP BY u.name ) A , ");
//		sql.append(" (SELECT ").append(" u.name, SUM(paysum-refundsum) AS r2 ");
//		sql.append(" FROM ( SELECT contract_id  AS contractid , SUM(DECODE(h.paytype,'Pay',h.pay,0)) AS paysum ,");
//		sql.append(" SUM(DECODE(h.paytype,'Refund',h.pay,0)) AS refundsum  FROM ef_payhistory    h ");
//		sql.append(" WHERE h.paytime BETWEEN TO_DATE('").append(strLastStart).append("','yyyy-MM-dd')");
//		sql.append(" AND TO_DATE('").append(strLastEnd).append("','yyyy-MM-dd') ");
//		sql.append(" GROUP BY h.contract_id )    p ,");
//		sql.append(" ef_contract c ,ef_leads l, ef_user u WHERE ");
//		sql.append(" p.contractid = c.id ");
//		sql.append(" AND c.leads_id = l.id ");
//		sql.append(" AND l.cc_id = u.id ");
//		sql.append(" GROUP BY u.name ) B   ");
//		sql.append(" WHERE A.name = B.name(+) ");
		SQLQuery query = this.getSession().createSQLQuery(sql.toString());
		query.addScalar("name");
		query.addScalar("sum1");
		query.addScalar("sum2");

		List<Object> list = query.list();
		for(int i=0;i<list.size();i++){
			Object[] s = (Object[]) list.get(i);
			RevenueCount revenue = new RevenueCount();
			revenue.setTitle(s[0].toString());
			if (s[1] != null) {
				revenue.setCurrentSum(Double.valueOf(s[1].toString()));
			} else {
				revenue.setCurrentSum(0.0);
			}
			if (s[2] != null)
				revenue.setLastSum(Double.valueOf(s[2].toString()));
			else
				revenue.setLastSum(0.0);
			result.add(revenue);
		}
		 
		
		return result;
	}
	
	/**
	 * Revenue By Ad Channel
	 */
	public List<RevenueCount> sumByChannel(Date start,Date end, Long schoolId, String channelList){
		SimpleDateFormat formater = new SimpleDateFormat("yyyyMMdd");
		SimpleDateFormat formater2 = new SimpleDateFormat("yyyy-MM-dd");
		String _start = formater.format(start);
		int _start_year = Integer.valueOf(_start.substring(0, 4));
		String _end = formater.format(end);
		int _end_year = Integer.valueOf(_end.substring(0, 4));
		Date lastStart = null;
		Date lastEnd = null;
		try {
		  lastStart =	formater.parse((_start_year-1)+_start.substring(4));
		  lastEnd = formater.parse((_end_year-1)+_end.substring(4));
		} catch (ParseException e) {
			 
			e.printStackTrace();
		}
		String strStart = formater2.format(start);
		String strEnd = formater2.format(end);
		String strLastStart = formater2.format(lastStart);
		String strLastEnd = formater2.format(lastEnd);
		List<RevenueCount> result = new ArrayList<RevenueCount>();
		StringBuilder sql = new StringBuilder();
		sql.append("select e.name, e.pay as sum1, f.pay as sum2 from (select c.name, d.pay, c.id from (select * from ef_adchannel where");
		if(channelList!=null) {
			sql.append(" id in (");
			sql.append(channelList);
			sql.append(") and ");
		}
		sql.append(" status='VALID') c left join (select sum(case when paytype='Pay' then pay else -pay end) as pay, h.adchannel_id from ef_payhistory a, ef_contract b, ef_leads h where h.school_id=");
		sql.append(schoolId.toString());
		sql.append(" and a.contract_id=b.id and b.leads_id=h.id and a.paytime<=to_date('");
		sql.append(strEnd);
		sql.append(" 23:59:59','yyyy-MM-dd hh24:mi:ss') and a.paytime>=to_date('");
		sql.append(strStart);
		sql.append("','yyyy-MM-dd') group by adchannel_id ) d on c.id=d.adchannel_id) e left join(select c.name, d.pay, c.id from ef_adchannel c left join (select sum(case when paytype='Pay' then pay else -pay end) as pay, h.adchannel_id from ef_payhistory a, ef_contract b, ef_leads h where h.school_id=");
		sql.append(schoolId.toString());
		sql.append(" and a.contract_id=b.id  and b.leads_id=h.id and a.paytime<=to_date('");
		sql.append(strLastEnd);
		sql.append(" 23:59:59','yyyy-MM-dd hh24:mi:ss') and a.paytime>=to_date('");
		sql.append(strLastStart);
		sql.append("','yyyy-MM-dd') group by adchannel_id ) d on c.id=d.adchannel_id) f on e.id=f.id");
		SQLQuery query = this.getSession().createSQLQuery(sql.toString());
		query.addScalar("name");
		query.addScalar("sum1");
		query.addScalar("sum2");
		List<Object> list = query.list();
		for(int i=0;i<list.size();i++){
			Object[] s = (Object[]) list.get(i);
			RevenueCount revenue = new RevenueCount();
			revenue.setTitle(s[0].toString());
			if (s[1] != null) {
				revenue.setCurrentSum(Double.valueOf(s[1].toString()));
			} else {
				revenue.setCurrentSum(0.0);
			}
			if (s[2] != null)
				revenue.setLastSum(Double.valueOf(s[2].toString()));
			else
				revenue.setLastSum(0.0);
			result.add(revenue);
		}
		 
		
		return result;
	}
	
	/**
	 * Revenue By Product
	 */
	public List<RevenueCount> sumByCourse(Date start,Date end, Long schoolId, String courseList){
		SimpleDateFormat formater = new SimpleDateFormat("yyyyMMdd");
		SimpleDateFormat formater2 = new SimpleDateFormat("yyyy-MM-dd");
		String _start = formater.format(start);
		int _start_year = Integer.valueOf(_start.substring(0, 4));
		String _end = formater.format(end);
		int _end_year = Integer.valueOf(_end.substring(0, 4));
		Date lastStart = null;
		Date lastEnd = null;
		try {
		  lastStart =	formater.parse((_start_year-1)+_start.substring(4));
		  lastEnd = formater.parse((_end_year-1)+_end.substring(4));
		} catch (ParseException e) {
			 
			e.printStackTrace();
		}
		String strStart = formater2.format(start);
		String strEnd = formater2.format(end);
		String strLastStart = formater2.format(lastStart);
		String strLastEnd = formater2.format(lastEnd);
		List<RevenueCount> result = new ArrayList<RevenueCount>();
		StringBuilder sql = new StringBuilder("select e.name, e.pay as sum1, f.pay as sum2 from (select * from (select c.name, c.id from ef_course c where");
		if(courseList!=null) {
			sql.append(" id in (");
			sql.append(courseList);
			sql.append(") and ");
		}
		sql.append(" c.status='VALID') c left join (select sum(case when paytype='Pay' then pay else -pay end) as pay, b.course_id from ef_payhistory a, ef_contract b, ef_user c where b.createuser_id=c.id and c.school_id=");
		sql.append(schoolId);
		sql.append(" and a.contract_id=b.id and paytime<=to_date('");
		sql.append(strEnd);
		sql.append(" 23:59:59','yyyy-MM-dd hh24:mi:ss') and paytime>=to_date('");
		sql.append(strStart);
		sql.append("','yyyy-MM-dd') group by course_id ) d  on c.id=d.course_id) e left join (");
		
		sql.append("select c.name, d.pay, c.id from ef_course c left join (select sum(case when paytype='Pay' then pay else -pay end) as pay, b.course_id from ef_payhistory a, ef_contract b, ef_user c where b.createuser_id=c.id and c.school_id=");
		sql.append(schoolId);
		sql.append(" and a.contract_id=b.id and paytime<=to_date('");
		sql.append(strLastEnd);
		sql.append(" 23:59:59','yyyy-MM-dd hh24:mi:ss') and paytime>=to_date('");
		sql.append(strLastStart);
		sql.append("','yyyy-MM-dd') group by course_id ) d  on c.id=d.course_id) f on e.id=f.id");
		SQLQuery query = this.getSession().createSQLQuery(sql.toString());
		query.addScalar("name");
		query.addScalar("sum1");
		query.addScalar("sum2");
		List<Object> list = query.list();
		for(int i=0;i<list.size();i++){
			Object[] s = (Object[]) list.get(i);
			RevenueCount revenue = new RevenueCount();
			revenue.setTitle(s[0].toString());
			if (s[1] != null) {
				revenue.setCurrentSum(Double.valueOf(s[1].toString()));
			} else {
				revenue.setCurrentSum(0.0);
			}
			if (s[2] != null)
				revenue.setLastSum(Double.valueOf(s[2].toString()));
			else
				revenue.setLastSum(0.0);
			result.add(revenue);
		}
		 
		
		return result;
	}

	/**
	 * Deposit By CC
	 */
	public List<RevenueCount> depositByCC(Date start,Date end, Long schoolId, String ccList){
		SimpleDateFormat formater = new SimpleDateFormat("yyyyMMdd");
		SimpleDateFormat formater2 = new SimpleDateFormat("yyyy-MM-dd");
		String _start = formater.format(start);
		int _start_year = Integer.valueOf(_start.substring(0, 4));
		String _end = formater.format(end);
		int _end_year = Integer.valueOf(_end.substring(0, 4));
		Date lastStart = null;
		Date lastEnd = null;
		try {
		  lastStart =	formater.parse((_start_year-1)+_start.substring(4));
		  lastEnd = formater.parse((_end_year-1)+_end.substring(4));
		} catch (ParseException e) {
			 
			e.printStackTrace();
		}
		String strStart = formater2.format(start);
		String strEnd = formater2.format(end);
		String strLastStart = formater2.format(lastStart);
		String strLastEnd = formater2.format(lastEnd);
		List<RevenueCount> result = new ArrayList<RevenueCount>();
//		StringBuilder sql = new StringBuilder();
//		sql.append(" select i.name, sum1, sum2 from (select e.name, e.role_id, e.pay as sum1, f.pay as sum2 from (select c.name, c.role_id, d.pay, d.cc_id from ef_user c left join (select sum(a.deposit) as pay, cc_id from ef_leads a where a.deposittime<=to_date('");
//		sql.append(strEnd);
//		sql.append(" 23:59:59','yyyy-MM-dd hh24:mi:ss') and a.deposittime>=to_date('");
//		sql.append(strStart);
//		sql.append("','yyyy-MM-dd hh24:mi:ss') group by a.cc_id ) d on c.id=d.cc_id) e left join (select sum(a.deposit) as pay, a.cc_id from ef_leads a where a.deposittime<=to_date('");
//		sql.append(strLastEnd);
//		sql.append(" 23:59:59','yyyy-MM-dd hh24:mi:ss') and a.deposittime>=to_date('");
//		sql.append(strLastStart);
//		sql.append("','yyyy-MM-dd hh24:mi:ss') group by a.cc_id ) f on e.cc_id=f.cc_id ) i, ef_role j where i.role_id=j.id and j.name='CC' and i.status='VALID'");
		
		StringBuffer sql = new StringBuffer("select name, e.pay sum1, b.pay sum2  from (select * from (select a.id, a.name from ef_user a, ef_role b where");
		if(ccList!=null) {
			sql.append(" a.id in (");
			sql.append(ccList);
			sql.append(") and");
		}
		sql.append(" a.school_id=");
		sql.append(schoolId);
		sql.append(" and a.role_id=b.id and a.status='VALID' and b.name='CC') c left join (select sum(a.deposit) as pay, cc_id from ef_leads a where a.deposittime<=to_date('");
		sql.append(strEnd);
		sql.append(" 23:59:59','yyyy-MM-dd hh24:mi:ss') and deposittime>=to_date('");
		sql.append(strStart);
		sql.append("','yyyy-MM-dd') group by  a.cc_id) d  on c.id=d.cc_id) e left join (select sum(a.deposit) as pay, a.cc_id from ef_leads a where a.deposittime<=to_date('");
		sql.append(strLastEnd);
		sql.append(" 23:59:59','yyyy-MM-dd hh24:mi:ss') and deposittime>=to_date('");
		sql.append(strLastStart);
		sql.append("','yyyy-MM-dd') group by a.cc_id ) b on e.id=b.cc_id");
		SQLQuery query = this.getSession().createSQLQuery(sql.toString());
		query.addScalar("name");
		query.addScalar("sum1");
		query.addScalar("sum2");
		List<Object> list = query.list();
		for(int i=0;i<list.size();i++){
			Object[] s = (Object[]) list.get(i);
			RevenueCount revenue = new RevenueCount();
			revenue.setTitle(s[0].toString());
			if (s[1] != null) {
				revenue.setCurrentSum(Double.valueOf(s[1].toString()));
			} else {
				revenue.setCurrentSum(0.0);
			}
			if (s[2] != null)
				revenue.setLastSum(Double.valueOf(s[2].toString()));
			else
				revenue.setLastSum(0.0);
			result.add(revenue);
		}
		 
		
		return result;
	}

	public List<CCPerformance> performanceByCC(String month, Long schoolId){
		//SimpleDateFormat formater = new SimpleDateFormat("yyyy-MM");
		String strMonth = month;
		List<CCPerformance> result = new ArrayList<CCPerformance>();
		
		StringBuilder sql = new StringBuilder();
		sql.append("select name, pay, count_call, count_contract from (select * from (select a.id, a.name from ef_user a, ef_role b where a.school_id=");
		sql.append(schoolId);
		sql.append(" and a.role_id=b.id and b.name='CC') a left join (select sum(case when paytype='Pay' then pay else -pay end) as pay, b.createuser_id ,count(createuser_id) as count_contract from ef_payhistory a, ef_contract b where a.contract_id=b.id and '");
		sql.append(strMonth);
		sql.append("'=to_char(paytime,'yyyy-MM') group by createuser_id) d on a.id=d.createuser_id) e left join (select count(*) as count_call, operater_id from ef_leadseventhistory where to_char(dealtime, 'yyyy-MM')='");
		sql.append(strMonth);
		sql.append("' group by operater_id) f on e.id=f.operater_id");
		
		Query query = this.getSession().createSQLQuery(sql.toString());
		List list = query.list();
		for(int i=0;i<list.size();i++){
			Object[] s = (Object[]) list.get(i);
			CCPerformance ccp = new CCPerformance();
			ccp.setTitle(s[0].toString());
			if(s[1]!=null)
				ccp.setRevenue(Double.valueOf(s[1].toString()));
			else
				ccp.setRevenue(0.0);
			if(s[2]!=null)
				ccp.setCall(Integer.valueOf(s[2].toString()));
			else
				ccp.setCall(0);
			if(s[3]!=null){
				ccp.setContract(Integer.valueOf(s[3].toString()));
			}
			else
				ccp.setContract(0);
			if(ccp.getCall()==0){
				ccp.setRate(0.0);
			}else{
				ccp.setRate(Double.valueOf(ccp.getContract()/ccp.getCall())*100);
			}
			
			result.add(ccp);
		}
		
		return result;
	}
	
	public List<CRPerformance> performanceByCR(String month, Long schoolId){
	//	SimpleDateFormat formater = new SimpleDateFormat("yyyy-MM");
		String strMonth = month;
		List<CRPerformance> result  = new ArrayList<CRPerformance>();
		StringBuilder sql = new StringBuilder();
		sql.append(" select name, sum2, sum1 from (select a.* from ef_user a, ef_role b where a.school_id=");
		sql.append(schoolId);
		sql.append(" and a.role_id = b.id and b.name='CR' and a.status='VALID') left join(select a.showdealuser_id, count(*) sum1 from ef_leads a where a.leadsstatus='ShowUp' and to_char(a.showtime, 'yyyy-MM')='");
		sql.append(strMonth);
		sql.append("' group by a.showdealuser_id) on id=showdealuser_id left join (select count(*) sum2, a.operater_id from ef_leadseventhistory a where to_char(a.dealtime, 'yyyy-MM')='");
		sql.append(strMonth);
		sql.append("' group by a.operater_id) on id=operater_id ");
		
		Query query = this.getSession().createSQLQuery(sql.toString());
		List list = query.list();
		for(int i=0;i<list.size();i++){
			Object[] s = (Object[]) list.get(i);
			CRPerformance crp = new CRPerformance();
			crp.setTitle(s[0].toString());
			 
			if(s[1]!=null)
				crp.setCall(Integer.valueOf(s[1].toString()));
			if(s[2]!=null){
				crp.setShowup(Integer.valueOf(s[2].toString()));
			}
			 
			result.add(crp);
		}
		return result;
	}
	
	public List<TMPerformance> performanceByTM(String month, Long schoolId){
		//SimpleDateFormat formater = new SimpleDateFormat("yyyy-MM");
		String strMonth = month;
		List<TMPerformance> result = new ArrayList<TMPerformance>();
		
		StringBuilder sql = new StringBuilder();
		sql.append(" select name, sum3, sum1, sum4, sum2 from (select a.id, a.name from ef_user a, ef_role b where ");
		sql.append(" a.role_id=b.id and a.status='VALID' and b.name='TM') a left join(select count(*) sum1, tm_id from ef_leads where to_char(setappttime, 'yyyy-MM-dd')='");
		sql.append(strMonth);
		sql.append("' group by tm_id) b on a.id=b.tm_id left join (select count(*) sum2, tm_id from ef_leads where to_char(showtime, 'yyyy-MM-dd')='");
		sql.append(strMonth);
		sql.append("' group by tm_id) c on a.id=c.tm_id left join (select count(*) sum3, operater_id from (select operater_id, leads_id from ef_leadseventhistory where to_char(dealtime,'yyyy-MM-dd')='");
		sql.append(strMonth);
		sql.append("' group by operater_id, leads_id) group by operater_id) d on a.id=d.operater_id left join (select count(*) sum4, tm_id from ef_leads where tmcall=0 or tmcall is null group by tm_id) e on a.id=e.tm_id");
				
		
		Query query = this.getSession().createSQLQuery(sql.toString());
		List list = query.list();
		for(int i=0;i<list.size();i++){
			Object[] s = (Object[]) list.get(i);
			TMPerformance tmp = new TMPerformance();
			tmp.setTitle(s[0].toString());
			 
			if(s[1]!=null)
				tmp.setCall(Integer.valueOf(s[1].toString()));
			if(s[2]!=null){
				tmp.setSetappt(Integer.valueOf(s[2].toString()));
			}
			if(s[3]!=null){
				tmp.setUncall(Integer.valueOf(s[3].toString()));
			}
			if(s[4]!=null){
				tmp.setShowup(Integer.valueOf(s[4].toString()));
			}
			 
			result.add(tmp);
		}
		return result;
	}
	
	public List<Financial> financial(String year, Long schoolId){
		//SimpleDateFormat formater = new SimpleDateFormat("yyyy");
		String strYear =  year;
		List<Financial> result = new ArrayList<Financial>();
		StringBuilder sql = new StringBuilder();
		sql.append("select month_char, s1, s2, s3, s4 from EF_MONTH a left join (SELECT SUBSTR(TO_CHAR(p.paytime,'yyyyMM'),5,2) AS m ,SUM(p.pay) AS s1 FROM  ef_payhistory p, ef_contract q, ef_user r WHERE p.contract_id=q.id and q.createuser_id=r.id and r.school_id=");
		sql.append(schoolId);
		sql.append(" and   TO_CHAR(p.paytime,'yyyy')='");
		sql.append(strYear);
		sql.append("' AND p.paytype='Pay'  GROUP BY SUBSTR(TO_CHAR(p.paytime,'yyyyMM'),5,2)) b on a.month_char=b.m left join ( SELECT SUBSTR(TO_CHAR(p.paytime,'yyyyMM'),5,2) AS m ,SUM(p.pay) AS s2  FROM  ef_payhistory p, ef_contract q, ef_user r WHERE p.contract_id=q.id and q.createuser_id=r.id and r.school_id=");
		sql.append(schoolId);
		sql.append(" and TO_CHAR(p.paytime,'yyyy')='");
		sql.append(strYear);
		sql.append("' AND p.paytype='Refund'  GROUP BY SUBSTR(TO_CHAR(p.paytime,'yyyyMM'),5,2)) c on a.month_char=c.m left join(SELECT SUBSTR(TO_CHAR(c.createtime,'yyyyMM'),5,2) As m ,COUNT(*) AS s3 FROM  ef_contract c, ef_user d WHERE c.createuser_id=d.id and d.school_id=");
		sql.append(schoolId);
		sql.append(" and TO_CHAR(c.createtime,'yyyy')='");
		sql.append(strYear);
		sql.append("' AND c.status='VALID'  GROUP BY SUBSTR(TO_CHAR(c.createtime,'yyyyMM'),5,2) ) d on a.month_char=d.m left join (SELECT SUBSTR(TO_CHAR(c.createtime,'yyyyMM'),5,2) As m ,COUNT(*) AS s4 FROM  ef_contract c , ef_user d WHERE c.createuser_id=d.id and d.school_id=");
		sql.append(schoolId);
		sql.append(" and  TO_CHAR(c.createtime,'yyyy')='");
		sql.append(strYear);
		sql.append("' AND c.status='INVALID'  GROUP BY SUBSTR(TO_CHAR(c.createtime,'yyyyMM'),5,2) ) e on a.month_char=e.m order by month_char");
		
		Query query = this.getSession().createSQLQuery(sql.toString());
		List list = query.list();
		for(int i=0;i<list.size();i++){
			Object[] s = (Object[]) list.get(i);
			Financial f = new Financial();
			String month = "";
			switch(Integer.valueOf(s[0].toString())){
			case 1:
				month = "January";break;
			case 2:
				month = "February";break;
			case 3:
				month= "March"; break;
			case 4: 
				month ="April"; break;
			case 5:
				month= "May"; break;
			case 6:
				month= "June"; break;
			case 7:
				month = "July"; break;
			case 8:
				month = "August";break;
			case 9:
				month = "September";break;
			case 10:
				month = "October";break;
			case 11:
				month= "November";break;
			case 12:
				month= "December";
			}
			f.setMonth(month);
			 
			if(s[1]!=null)
				f.setPaid(Double.valueOf(s[1].toString()));
			if(s[2]!=null){
				f.setRefund(Double.valueOf(s[2].toString()));
			}
			if(s[3]!=null){
				f.setValid(Integer.valueOf(s[3].toString()));
			}
			if(s[4]!=null){
				f.setInvalid(Integer.valueOf(s[4].toString()));
			}
			f.setSum(f.getPaid()-f.getRefund());
			 
			result.add(f);
		}
		
		return result;
	}
}
